﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Data.SqlClient;

using SIPO.Library;
using SIPO.Library.DomainObject.Transaction;
using SIPO.Library.Enum;
using SIPO.Library.Interfaces;
using SIPO.Library.DomainObject.Administration.Catalog;

namespace SIPO.Library.DataAccess.Transaction
{
    public partial class JobDescriptionRepo : ISearchable<JobDescription>
    {
        public object SelectScalar(SQL.Function.Aggregate function, string column,string parameters)
        {
            object _result = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    StringBuilder sbQuery = new StringBuilder();
                    switch (function)
                    {
                        case SQL.Function.Aggregate.Max:
                            sbQuery.AppendFormat("SELECT MAX({0}) FROM transactions.JobDescription {1}", column,parameters);
                            break;
                        case SQL.Function.Aggregate.Min:
                            sbQuery.AppendFormat("SELECT MIN({0}) FROM transactions.JobDescription  {1}", column, parameters);
                            break;
                        case SQL.Function.Aggregate.Distinct:
                            sbQuery.AppendFormat("SELECT DISTINCT({0}) FROM transactions.JobDescription  {1}", column, parameters);
                            break;
                        case SQL.Function.Aggregate.Count:
                            sbQuery.AppendFormat("SELECT COUNT({0}) FROM transactions.JobDescription A INNER JOIN master.Position B ON A.PositionID=B.PositionID  INNER JOIN master.Department C ON A.DepartmentID = C.DeptID {1}", column, parameters);
                            break;
                        case SQL.Function.Aggregate.Sum:
                            sbQuery.AppendFormat("SELECT SUM({0}) FROM transactions.JobDescription  {1}", column, parameters);
                            break;
                        case SQL.Function.Aggregate.Avg:
                            sbQuery.AppendFormat("SELECT AVG({0}) FROM transactions.JobDescription  {1}", column, parameters);
                            break;
                        default:
                            // do nothing 
                            break;
                    }

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        _result = cmd.ExecuteScalar();
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return _result;
        }


     

        public List<JobDescription> GetList(int pageSize, int currentPage, string sortName, string sortOrder,string parameters)
        {
            List<JobDescription> tt = new List<JobDescription>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    int startRow = ((currentPage - 1) * pageSize) + 1;
                    int endRow = (startRow + pageSize);

                    StringBuilder sbQuery = new StringBuilder();
                    sbQuery.AppendFormat(" WITH result_set AS ");
                    sbQuery.AppendFormat(" ( ");
                    sbQuery.AppendFormat("    SELECT ");
                    sbQuery.AppendFormat("      ROW_NUMBER() OVER (ORDER BY {0} {1}) AS [row_number], ", sortName, sortOrder);
                    sbQuery.AppendFormat("      A.JobDescID, A.RowStatus, A.PositionID,A.DepartmentID, A.Location, A.JobPurpose, A.Capex, A.Opex, A.DirectSubordinates, A.IndirectSubordinates, A.AppAccountID, A.CreatedBy, A.CreatedDate, A.UpdatedBy, A.UpdatedDate,A.ReviewBy,A.ReviewDate,B.PositionName,C.DepartmentDesc,A.ApprovedBy,A.ApprovedDate");
                    sbQuery.AppendFormat("    FROM ");
                    sbQuery.AppendFormat("      transactions.JobDescription A INNER JOIN master.Position B ON A.PositionID=B.PositionID INNER JOIN master.Department C ON A.DepartmentID = C.DeptID ");
                    sbQuery.AppendFormat("      {0} ",parameters);
                    sbQuery.AppendFormat(" ) ");
                    sbQuery.AppendFormat(" SELECT * FROM result_set WHERE [row_number] BETWEEN {0} AND {1} ", startRow, endRow);

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                JobDescription t = new JobDescription(true);
                                t.JobDescID = dr[JobDescription.Fields.JobDescID] == DBNull.Value ? 0 : int.Parse(dr[JobDescription.Fields.JobDescID].ToString());
                                t.RowStatus = dr[JobDescription.Fields.RowStatus] == DBNull.Value ? false : bool.Parse(dr[JobDescription.Fields.RowStatus].ToString());
                                t.PositionID = dr[JobDescription.Fields.PositionID] == DBNull.Value ? 0 : int.Parse(dr[JobDescription.Fields.PositionID].ToString());
                                t.Location = dr[JobDescription.Fields.Location] == DBNull.Value ? string.Empty : dr[JobDescription.Fields.Location].ToString();
                                t.JobPurpose = dr[JobDescription.Fields.JobPurpose] == DBNull.Value ? string.Empty : dr[JobDescription.Fields.JobPurpose].ToString();
                                t.Capex = dr[JobDescription.Fields.Capex] == DBNull.Value ? string.Empty : dr[JobDescription.Fields.Capex].ToString();
                                t.Opex = dr[JobDescription.Fields.Opex] == DBNull.Value ? string.Empty : dr[JobDescription.Fields.Opex].ToString();
                                t.DirectSubordinates = dr[JobDescription.Fields.DirectSubordinates] == DBNull.Value ? string.Empty : dr[JobDescription.Fields.DirectSubordinates].ToString();
                                t.IndirectSubordinates = dr[JobDescription.Fields.IndirectSubordinates] == DBNull.Value ? string.Empty : dr[JobDescription.Fields.IndirectSubordinates].ToString();
                                t.AppAccountID = dr[JobDescription.Fields.AppAccountID] == DBNull.Value ? (int?)null : int.Parse(dr[JobDescription.Fields.AppAccountID].ToString());
                                t.CreatedBy = dr[JobDescription.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[JobDescription.Fields.CreatedBy].ToString();
                                t.CreatedDate = dr[JobDescription.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[JobDescription.Fields.CreatedDate].ToString());
                                t.UpdatedBy = dr[JobDescription.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[JobDescription.Fields.UpdatedBy].ToString();
                                t.UpdatedDate = dr[JobDescription.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[JobDescription.Fields.UpdatedDate].ToString());
                                t.ReviewBy = dr[JobDescription.Fields.ReviewBy] == DBNull.Value ? string.Empty : dr[JobDescription.Fields.ReviewBy].ToString();
                                t.ReviewDate = dr[JobDescription.Fields.ReviewDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[JobDescription.Fields.ReviewDate].ToString());
                                t.ApprovedBy = dr[JobDescription.Fields.ApprovedBy] == DBNull.Value ? string.Empty : dr[JobDescription.Fields.ApprovedBy].ToString();
                                t.ApprovedDate = dr[JobDescription.Fields.ApprovedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[JobDescription.Fields.ApprovedDate].ToString());

                                t.DepartmentID = dr[JobDescription.Fields.DepartmentID] == DBNull.Value ? 0 : int.Parse(dr[JobDescription.Fields.DepartmentID].ToString());
                                t.Position.PositionName = dr[Position.Fields.PositionName] == DBNull.Value ? string.Empty : dr[Position.Fields.PositionName].ToString();
                                t.Department.DepartmentDesc = dr[Department.Fields.DepartmentDesc] == DBNull.Value ? string.Empty : dr[Department.Fields.DepartmentDesc].ToString();
                               
                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }

        public bool Save(JobDescription domain, List<JobDescRelationShipInternal> listInternal,List<JobDescRelationShipExternal> listExternal,List<JobDescResponsibility> listResponsibility,List<PerformanceIndicator> listPI,int programIDAktif)
        {
            bool result = false;

            using (SqlConnection conn = new SqlConnection(connectionString))
            {

                conn.Open();
                SqlTransaction txn = conn.BeginTransaction();
                try
                {
                    #region Insert Data Job Description

                    string query = string.Empty;
                    SqlCommand cmd = new SqlCommand(query, conn);
                    cmd.CommandText = "INSERT INTO transactions.JobDescription(RowStatus, PositionID, DepartmentID, Location, JobPurpose, Capex, Opex, DirectSubordinates, IndirectSubordinates, AppAccountID, CreatedBy, CreatedDate, UpdatedBy, UpdatedDate,ProgramID,FunctionalCompetency,Challenge,Authority) VALUES(@RowStatus, @PositionID, @DepartmentID, @Location, @JobPurpose, @Capex, @Opex, @DirectSubordinates, @IndirectSubordinates, @AppAccountID, @CreatedBy, @CreatedDate, @UpdatedBy, @UpdatedDate,@ProgramID,@FunctionalCompetency,@Challenge,@Authority)";
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@RowStatus", SqlDbType.Bit).Value = domain.RowStatus;
                    cmd.Parameters.Add("@PositionID", SqlDbType.Int).Value = domain.PositionID;
                    cmd.Parameters.Add("@DepartmentID", SqlDbType.Int).Value = (object)domain.DepartmentID ?? DBNull.Value;
                    cmd.Parameters.Add("@Location", SqlDbType.VarChar).Value = (object)domain.Location ?? DBNull.Value;
                    cmd.Parameters.Add("@JobPurpose", SqlDbType.Text).Value = (object)domain.JobPurpose ?? DBNull.Value;
                    cmd.Parameters.Add("@Capex", SqlDbType.VarChar).Value = (object)domain.Capex ?? DBNull.Value;
                    cmd.Parameters.Add("@Opex", SqlDbType.VarChar).Value = (object)domain.Opex ?? DBNull.Value;
                    cmd.Parameters.Add("@DirectSubordinates", SqlDbType.VarChar).Value = (object)domain.DirectSubordinates ?? DBNull.Value;
                    cmd.Parameters.Add("@IndirectSubordinates", SqlDbType.VarChar).Value = (object)domain.IndirectSubordinates ?? DBNull.Value;
                    cmd.Parameters.Add("@AppAccountID", SqlDbType.Int).Value = (object)domain.AppAccountID ?? DBNull.Value;
                    cmd.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)domain.CreatedBy ?? DBNull.Value;
                    cmd.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)domain.CreatedDate ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)domain.UpdatedBy ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)domain.UpdatedDate ?? DBNull.Value;
                    cmd.Parameters.Add("@ProgramID", SqlDbType.Int).Value = programIDAktif;
                    cmd.Parameters.Add("@FunctionalCompetency", SqlDbType.VarChar).Value = (object)domain.FunctionalCompetency ?? DBNull.Value;
                    cmd.Parameters.Add("@Challenge", SqlDbType.VarChar).Value = (object)domain.Challenge ?? DBNull.Value;
                    cmd.Parameters.Add("@Authority", SqlDbType.VarChar).Value = (object)domain.Authority ?? DBNull.Value;

                    cmd.Transaction = txn;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();

                    #endregion


                    #region Ambil JobDescID

                    string queryjobdescid = @"SELECT JobDescID FROM transactions.JobDescription WHERE CreatedBy=@CreatedBy AND CreatedDate=@CreatedDate";

                    SqlCommand cmdJobDesc = new SqlCommand(queryjobdescid, conn);
                    cmdJobDesc.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = domain.CreatedBy;
                    cmdJobDesc.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = domain.CreatedDate;
                    cmdJobDesc.Transaction = txn;
                    var jobdesid = cmdJobDesc.ExecuteScalar();
                    int resultjobdesid = jobdesid == DBNull.Value ? 0 : int.Parse(jobdesid.ToString());
                    cmdJobDesc.Dispose();

                    #endregion

                    #region Insert Data Internal

                    foreach (JobDescRelationShipInternal data in listInternal)
                    {


                        query = "INSERT INTO transactions.JobDescRelationShipInternal(JobDescriptionID, Department, Purpose, CreatedBy, CreatedDate, UpdatedBy, UpdatedDate,ProgramID) VALUES(@JobDescriptionID, @Department, @Purpose, @CreatedBy, @CreatedDate, @UpdatedBy, @UpdatedDate,@ProgramID)";
                        SqlCommand cmdInternal = new SqlCommand(query, conn);
                        cmdInternal.CommandType = CommandType.Text;
                        cmdInternal.Parameters.Add("@JobDescriptionID", SqlDbType.Int).Value = resultjobdesid;
                        cmdInternal.Parameters.Add("@Department", SqlDbType.VarChar).Value = data.Department;
                        cmdInternal.Parameters.Add("@Purpose", SqlDbType.Text).Value = (object)data.Purpose ?? DBNull.Value;
                        cmdInternal.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)data.CreatedBy ?? DBNull.Value;
                        cmdInternal.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)data.CreatedDate ?? DBNull.Value;
                        cmdInternal.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)data.UpdatedBy ?? DBNull.Value;
                        cmdInternal.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)data.UpdatedDate ?? DBNull.Value;

                        cmdInternal.Parameters.Add("@ProgramID", SqlDbType.Int).Value = programIDAktif;

                        cmdInternal.Transaction = txn;
                        cmdInternal.ExecuteNonQuery();
                        cmdInternal.Dispose();
                    }

                    #endregion

                    #region Insert Data External

                    foreach (JobDescRelationShipExternal data in listExternal)
                    {


                        query = "INSERT INTO transactions.JobDescRelationShipExternal(JobDescriptionID, Department, Purpose, CreatedBy, CreatedDate, UpdatedBy, UpdatedDate,ProgramID) VALUES(@JobDescriptionID, @Department, @Purpose, @CreatedBy, @CreatedDate, @UpdatedBy, @UpdatedDate,@ProgramID)";
                        SqlCommand cmdExternal = new SqlCommand(query, conn);
                        cmdExternal.CommandType = CommandType.Text;
                        cmdExternal.Parameters.Add("@JobDescriptionID", SqlDbType.Int).Value = resultjobdesid;
                        cmdExternal.Parameters.Add("@Department", SqlDbType.VarChar).Value = data.Department;
                        cmdExternal.Parameters.Add("@Purpose", SqlDbType.Text).Value = (object)data.Purpose ?? DBNull.Value;
                        cmdExternal.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)data.CreatedBy ?? DBNull.Value;
                        cmdExternal.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)data.CreatedDate ?? DBNull.Value;
                        cmdExternal.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)data.UpdatedBy ?? DBNull.Value;
                        cmdExternal.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)data.UpdatedDate ?? DBNull.Value;

                        cmdExternal.Parameters.Add("@ProgramID", SqlDbType.Int).Value = programIDAktif;

                        cmdExternal.Transaction = txn;
                        cmdExternal.ExecuteNonQuery();
                        cmdExternal.Dispose();
                    }

                    #endregion

                    #region Insert Data Responsibility

                    foreach (JobDescResponsibility data in listResponsibility)
                    {
                       
                        query = "INSERT INTO transactions.JobDescResponsibility(JobDescriptionID, MainTask, Activities, CreatedBy, CreatedDate, UpdatedBy, UpdatedDate,ProgramID) VALUES(@JobDescriptionID, @MainTask, @Activities, @CreatedBy, @CreatedDate, @UpdatedBy, @UpdatedDate,@ProgramID)";
                        SqlCommand cmdResponsibility = new SqlCommand(query, conn);
                        cmdResponsibility.CommandType = CommandType.Text;
                        cmdResponsibility.Parameters.Add("@JobDescriptionID", SqlDbType.Int).Value = resultjobdesid;
                        cmdResponsibility.Parameters.Add("@MainTask", SqlDbType.VarChar).Value = (object)data.MainTask ?? DBNull.Value;
                        cmdResponsibility.Parameters.Add("@Activities", SqlDbType.Text).Value = (object)data.Activities ?? DBNull.Value;
                        cmdResponsibility.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)data.CreatedBy ?? DBNull.Value;
                        cmdResponsibility.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)data.CreatedDate ?? DBNull.Value;
                        cmdResponsibility.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)data.UpdatedBy ?? DBNull.Value;
                        cmdResponsibility.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)data.UpdatedDate ?? DBNull.Value;

                        cmdResponsibility.Parameters.Add("@ProgramID", SqlDbType.Int).Value = programIDAktif;

                        cmdResponsibility.Transaction = txn;
                        cmdResponsibility.ExecuteNonQuery();
                        cmdResponsibility.Dispose();
                    }

                    #endregion


                    #region Insert Data PI

                    foreach (PerformanceIndicator data in listPI)
                    {

                        #region Ambil ResponsibilityID

                        string queryRespon = @"SELECT ResponsibilityID FROM transactions.JobDescResponsibility WHERE MainTask=@MainTask AND JobDescriptionID=@JobDescriptionID ";

                        SqlCommand cmdRespon = new SqlCommand(queryRespon, conn);
                        cmdRespon.Parameters.Add("@MainTask", SqlDbType.VarChar).Value = data.ParentName;
                        cmdRespon.Parameters.Add("@JobDescriptionID", SqlDbType.VarChar).Value = resultjobdesid;

                        cmdRespon.Transaction = txn;
                        var responId = cmdRespon.ExecuteScalar();
                        int resultresponId = responId == DBNull.Value ? 0 : int.Parse(responId.ToString());
                        cmdRespon.Dispose();

                        #endregion


                        query = "INSERT INTO transactions.PerformanceIndicator(JobDescResponsibilityID, PI, CreatedBy, CreatedDate, UpdatedBy, UpdatedDate,ProgramID) VALUES(@JobDescResponsibilityID, @PI, @CreatedBy, @CreatedDate, @UpdatedBy, @UpdatedDate,@ProgramID)";
                        SqlCommand cmdPI = new SqlCommand(query, conn);
                        cmdPI.CommandType = CommandType.Text;
                        cmdPI.Parameters.Add("@JobDescResponsibilityID", SqlDbType.Int).Value = resultresponId;
                        cmdPI.Parameters.Add("@PI", SqlDbType.VarChar).Value = data.PI;
                        cmdPI.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)data.CreatedBy ?? DBNull.Value;
                        cmdPI.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)data.CreatedDate ?? DBNull.Value;
                        cmdPI.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)data.UpdatedBy ?? DBNull.Value;
                        cmdPI.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)data.UpdatedDate ?? DBNull.Value;

                        cmdPI.Parameters.Add("@ProgramID", SqlDbType.Int).Value = programIDAktif;

                        cmdPI.Transaction = txn;
                        cmdPI.ExecuteNonQuery();
                        cmdPI.Dispose();
                    }

                    #endregion

                    //#region Update Data JobTitle

                    //query = "UPDATE master.JobTitle SET MinEducation = @MinEducation, Major = @Major, WorkingExperience = @WorkingExperience,  FunctionalCompetency = @FunctionalCompetency, Challenge = @Challenge, Authority = @Authority, UpdatedDate = @UpdatedDate, UpdatedBy = @UpdatedBy WHERE PositionID = @PositionID AND ProgramID=@ProgramID";
                    //SqlCommand cmdJobTitle = new SqlCommand(query, conn);

                    //cmdJobTitle.Parameters.Add("@PositionID", SqlDbType.Int).Value = (object)objJobTitle.PositionID ?? DBNull.Value;
                    //cmdJobTitle.Parameters.Add("@MinEducation", SqlDbType.VarChar).Value = (object)objJobTitle.MinEducation ?? DBNull.Value;
                    //cmdJobTitle.Parameters.Add("@Major", SqlDbType.VarChar).Value = (object)objJobTitle.Major ?? DBNull.Value;
                    //cmdJobTitle.Parameters.Add("@WorkingExperience", SqlDbType.Text).Value = (object)objJobTitle.WorkingExperience ?? DBNull.Value;
                    //cmdJobTitle.Parameters.Add("@FunctionalCompetency", SqlDbType.Text).Value = (object)objJobTitle.FunctionalCompetency ?? DBNull.Value;
                    //cmdJobTitle.Parameters.Add("@Challenge", SqlDbType.Text).Value = (object)objJobTitle.Challenge ?? DBNull.Value;
                    //cmdJobTitle.Parameters.Add("@Authority", SqlDbType.Text).Value = (object)objJobTitle.Authority ?? DBNull.Value;
                    //cmdJobTitle.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)objJobTitle.UpdatedDate ?? DBNull.Value;
                    //cmdJobTitle.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)objJobTitle.UpdatedBy ?? DBNull.Value;

                    //cmdJobTitle.Parameters.Add("@ProgramID", SqlDbType.Int).Value = programIDAktif;

                    //cmdJobTitle.Transaction = txn;
                    //cmdJobTitle.ExecuteNonQuery();
                    //cmdJobTitle.Dispose();

                     
                    //#endregion


                    result = true;
                    txn.Commit();

                }
                catch (Exception ex)
                {
                    txn.Rollback();
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }
            }

            return result;
        }

        public bool DeleteWithRelationShip(int key)
        {
            bool result = false;

            using (SqlConnection conn = new SqlConnection(connectionString))
            {

                conn.Open();
                SqlTransaction txn = conn.BeginTransaction();
                try
                {
                    #region Delete Table RelationShip Internal

                    string query = "DELETE FROM transactions.JobDescRelationShipInternal WHERE JobDescriptionID = @JobDescriptionID";
                    SqlCommand cmd = new SqlCommand(query, conn);
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@JobDescriptionID", SqlDbType.Int).Value = key;

                    cmd.Transaction = txn;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();

                    #endregion

                    #region Delete Table RelationShip External

                    string queryExternal = "DELETE FROM transactions.JobDescRelationShipExternal WHERE JobDescriptionID = @JobDescriptionID";
                    SqlCommand cmdExternal = new SqlCommand(queryExternal, conn);
                    cmdExternal.CommandType = CommandType.Text;
                    cmdExternal.Parameters.Add("@JobDescriptionID", SqlDbType.Int).Value = key;

                    cmdExternal.Transaction = txn;
                    cmdExternal.ExecuteNonQuery();
                    cmdExternal.Dispose();

                    #endregion


                    #region Ambil ResponsibilityID

                    string queryRespon = @"SELECT ResponsibilityID FROM transactions.JobDescResponsibility WHERE JobDescriptionID=@JobDescriptionID";

                    SqlCommand cmdRespon = new SqlCommand(queryRespon, conn);
                    cmdRespon.Parameters.Add("@JobDescriptionID", SqlDbType.VarChar).Value = key;
                    cmdRespon.Transaction = txn;

                    DataSet ds = new DataSet();
                    SqlDataAdapter dap = new SqlDataAdapter(cmdRespon);
                    dap.Fill(ds);
                    dap.Dispose();
                    cmdRespon.Dispose();
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        #region Delete Table Performance Indicator

                        query = "DELETE FROM transactions.PerformanceIndicator WHERE JobDescResponsibilityID = @JobDescResponsibilityID";
                        SqlCommand cmdPI = new SqlCommand(query, conn);
                        cmdPI.CommandType = CommandType.Text;
                        cmdPI.Parameters.Add("@JobDescResponsibilityID", SqlDbType.Int).Value = Convert.ToInt16(dr[JobDescResponsibility.Fields.ResponsibilityID]);

                        cmdPI.Transaction = txn;
                        cmdPI.ExecuteNonQuery();
                        cmdPI.Dispose();

                        #endregion
                    }
                    

                  
                   

                    #endregion

                   

                    #region Delete Table Responsibility

                    query = "DELETE FROM transactions.JobDescResponsibility WHERE JobDescriptionID = @JobDescriptionID";
                    SqlCommand cmdResponsibility = new SqlCommand(query, conn);
                    cmdResponsibility.CommandType = CommandType.Text;
                    cmdResponsibility.Parameters.Add("@JobDescriptionID", SqlDbType.Int).Value = key;

                    cmdResponsibility.Transaction = txn;
                    cmdResponsibility.ExecuteNonQuery();
                    cmdResponsibility.Dispose();

                    #endregion

               

                    #region Delete Table JobDescription


                    string queryJobDesc = "DELETE FROM transactions.JobDescription WHERE JobDescID = @JobDescID";
                    SqlCommand cmdJobDesc = new SqlCommand(queryJobDesc, conn);
                    cmdJobDesc.CommandType = CommandType.Text;
                    cmdJobDesc.Parameters.Add("@JobDescID", SqlDbType.Int).Value = key;

                    cmdJobDesc.Transaction = txn;
                    cmdJobDesc.ExecuteNonQuery();
                    cmdJobDesc.Dispose();

                    #endregion


                    result = true;
                    txn.Commit();

                }
                catch (Exception ex)
                {
                    txn.Rollback();
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }
            }

            return result;
        }


        public bool Update(JobDescription domain, List<JobDescRelationShipInternal> listInternal, List<JobDescRelationShipExternal> listExternal,List<JobDescResponsibility> listResponsibility,List<PerformanceIndicator> listPI,ReviewJobDescription domainReview,int programIDAktif,string status)
        {
            bool result = false;

            using (SqlConnection conn = new SqlConnection(connectionString))
            {

                conn.Open();
                SqlTransaction txn = conn.BeginTransaction();
                try
                {
                    #region Update Data Job Description


                    string query = "UPDATE transactions.JobDescription SET RowStatus = @RowStatus, PositionID = @PositionID, DepartmentID = @DepartmentID, Location = @Location, JobPurpose = @JobPurpose, Capex = @Capex, Opex = @Opex, DirectSubordinates = @DirectSubordinates, IndirectSubordinates = @IndirectSubordinates, AppAccountID = @AppAccountID, UpdatedBy = @UpdatedBy, UpdatedDate = @UpdatedDate,ReviewBy=@ReviewBy,ReviewDate=@ReviewDate,FunctionalCompetency=@FunctionalCompetency,Challenge=@Challenge,Authority=@Authority WHERE JobDescID = @JobDescID AND ProgramID=@ProgramID";
                    SqlCommand cmd = new SqlCommand(query, conn);
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@JobDescID", SqlDbType.Int).Value = domain.JobDescID;
                    cmd.Parameters.Add("@RowStatus", SqlDbType.Bit).Value = domain.RowStatus;
                    cmd.Parameters.Add("@PositionID", SqlDbType.Int).Value = domain.PositionID;
                    cmd.Parameters.Add("@DepartmentID", SqlDbType.Int).Value = (object)domain.DepartmentID ?? DBNull.Value;
                    cmd.Parameters.Add("@Location", SqlDbType.VarChar).Value = (object)domain.Location ?? DBNull.Value;
                    cmd.Parameters.Add("@JobPurpose", SqlDbType.Text).Value = (object)domain.JobPurpose ?? DBNull.Value;
                    cmd.Parameters.Add("@Capex", SqlDbType.VarChar).Value = (object)domain.Capex ?? DBNull.Value;
                    cmd.Parameters.Add("@Opex", SqlDbType.VarChar).Value = (object)domain.Opex ?? DBNull.Value;
                    cmd.Parameters.Add("@DirectSubordinates", SqlDbType.VarChar).Value = (object)domain.DirectSubordinates ?? DBNull.Value;
                    cmd.Parameters.Add("@IndirectSubordinates", SqlDbType.VarChar).Value = (object)domain.IndirectSubordinates ?? DBNull.Value;
                    cmd.Parameters.Add("@AppAccountID", SqlDbType.Int).Value = (object)domain.AppAccountID ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)domain.UpdatedBy ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)domain.UpdatedDate ?? DBNull.Value;
                    cmd.Parameters.Add("@ReviewBy", SqlDbType.VarChar).Value = (object)domain.ReviewBy ?? DBNull.Value;
                    cmd.Parameters.Add("@ReviewDate", SqlDbType.DateTime).Value = (object)domain.ReviewDate ?? DBNull.Value;
                    cmd.Parameters.Add("@ProgramID", SqlDbType.Int).Value = programIDAktif;
                    cmd.Parameters.Add("@FunctionalCompetency", SqlDbType.VarChar).Value = (object)domain.FunctionalCompetency ?? DBNull.Value;
                    cmd.Parameters.Add("@Challenge", SqlDbType.VarChar).Value = (object)domain.Challenge ?? DBNull.Value;
                    cmd.Parameters.Add("@Authority", SqlDbType.VarChar).Value = (object)domain.Authority ?? DBNull.Value;

                    cmd.Transaction = txn;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();

                    #endregion


                    #region Hapus Semua Data Internal By JobDescID

                    string querydelete = @"DELETE FROM transactions.JobDescRelationShipInternal WHERE JobDescriptionID = @JobDescriptionID";

                    SqlCommand cmdDeleteInternal = new SqlCommand(querydelete, conn);
                    cmdDeleteInternal.Parameters.Add("@JobDescriptionID", SqlDbType.Int).Value = domain.JobDescID;
                    cmdDeleteInternal.Transaction = txn;
                    cmdDeleteInternal.ExecuteNonQuery();
                    cmdDeleteInternal.Dispose();

                    #endregion

                    #region Hapus Semua Data External By JobDescID

                    querydelete = @"DELETE FROM transactions.JobDescRelationShipExternal WHERE JobDescriptionID = @JobDescriptionID";

                    SqlCommand cmdDeleteExternal = new SqlCommand(querydelete, conn);
                    cmdDeleteExternal.Parameters.Add("@JobDescriptionID", SqlDbType.Int).Value = domain.JobDescID;
                    cmdDeleteExternal.Transaction = txn;
                    cmdDeleteExternal.ExecuteNonQuery();
                    cmdDeleteExternal.Dispose();

                    #endregion

                    #region Ambil ResponsibilityID

                    string queryRespon = @"SELECT ResponsibilityID FROM transactions.JobDescResponsibility WHERE JobDescriptionID=@JobDescriptionID";

                    SqlCommand cmdRespon = new SqlCommand(queryRespon, conn);
                    cmdRespon.Parameters.Add("@JobDescriptionID", SqlDbType.VarChar).Value = domain.JobDescID;
                    cmdRespon.Transaction = txn;

                    DataSet ds = new DataSet();
                    SqlDataAdapter dap = new SqlDataAdapter(cmdRespon);
                    dap.Fill(ds);
                    dap.Dispose();
                    cmdRespon.Dispose();
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        #region Delete Table Performance Indicator

                        query = "DELETE FROM transactions.PerformanceIndicator WHERE JobDescResponsibilityID = @JobDescResponsibilityID";
                        SqlCommand cmdPI = new SqlCommand(query, conn);
                        cmdPI.CommandType = CommandType.Text;
                        cmdPI.Parameters.Add("@JobDescResponsibilityID", SqlDbType.Int).Value = Convert.ToInt16(dr[JobDescResponsibility.Fields.ResponsibilityID]);

                        cmdPI.Transaction = txn;
                        cmdPI.ExecuteNonQuery();
                        cmdPI.Dispose();

                        #endregion
                    }





                    #endregion

                    #region Hapus Semua Data Responsibility By JobDescID

                    querydelete = @"DELETE FROM transactions.JobDescResponsibility WHERE JobDescriptionID = @JobDescriptionID";

                    SqlCommand cmdDeleteResponsibility = new SqlCommand(querydelete, conn);
                    cmdDeleteResponsibility.Parameters.Add("@JobDescriptionID", SqlDbType.Int).Value = domain.JobDescID;
                    cmdDeleteResponsibility.Transaction = txn;
                    cmdDeleteResponsibility.ExecuteNonQuery();
                    cmdDeleteResponsibility.Dispose();

                    #endregion


                    #region Insert Data Internal

                    foreach (JobDescRelationShipInternal data in listInternal)
                    {


                        query = "INSERT INTO transactions.JobDescRelationShipInternal(JobDescriptionID, Department, Purpose, CreatedBy, CreatedDate, UpdatedBy, UpdatedDate,ProgramID) VALUES(@JobDescriptionID, @Department, @Purpose, @CreatedBy, @CreatedDate, @UpdatedBy, @UpdatedDate,@ProgramID)";
                        SqlCommand cmdInternal = new SqlCommand(query, conn);
                        cmdInternal.CommandType = CommandType.Text;
                        cmdInternal.Parameters.Add("@JobDescriptionID", SqlDbType.Int).Value = domain.JobDescID;
                        cmdInternal.Parameters.Add("@Department", SqlDbType.VarChar).Value = data.Department;
                        cmdInternal.Parameters.Add("@Purpose", SqlDbType.Text).Value = (object)data.Purpose ?? DBNull.Value;
                        cmdInternal.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)data.CreatedBy ?? DBNull.Value;
                        cmdInternal.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)data.CreatedDate ?? DBNull.Value;
                        cmdInternal.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)data.UpdatedBy ?? DBNull.Value;
                        cmdInternal.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)data.UpdatedDate ?? DBNull.Value;

                        cmdInternal.Parameters.Add("@ProgramID", SqlDbType.Int).Value = programIDAktif;

                        cmdInternal.Transaction = txn;
                        cmdInternal.ExecuteNonQuery();
                        cmdInternal.Dispose();
                    }

                    #endregion

                    #region Insert Data External

                    foreach (JobDescRelationShipExternal data in listExternal)
                    {


                        query = "INSERT INTO transactions.JobDescRelationShipExternal(JobDescriptionID, Department, Purpose, CreatedBy, CreatedDate, UpdatedBy, UpdatedDate,ProgramID) VALUES(@JobDescriptionID, @Department, @Purpose, @CreatedBy, @CreatedDate, @UpdatedBy, @UpdatedDate,@ProgramID)";
                        SqlCommand cmdExternal = new SqlCommand(query, conn);
                        cmdExternal.CommandType = CommandType.Text;
                        cmdExternal.Parameters.Add("@JobDescriptionID", SqlDbType.Int).Value = domain.JobDescID;
                        cmdExternal.Parameters.Add("@Department", SqlDbType.VarChar).Value = data.Department;
                        cmdExternal.Parameters.Add("@Purpose", SqlDbType.Text).Value = (object)data.Purpose ?? DBNull.Value;
                        cmdExternal.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)data.CreatedBy ?? DBNull.Value;
                        cmdExternal.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)data.CreatedDate ?? DBNull.Value;
                        cmdExternal.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)data.UpdatedBy ?? DBNull.Value;
                        cmdExternal.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)data.UpdatedDate ?? DBNull.Value;

                        cmdExternal.Parameters.Add("@ProgramID", SqlDbType.Int).Value = programIDAktif;

                        cmdExternal.Transaction = txn;
                        cmdExternal.ExecuteNonQuery();
                        cmdExternal.Dispose();
                    }

                    #endregion

                    #region Insert Data Responsibility

                    foreach (JobDescResponsibility data in listResponsibility)
                    {

                        query = "INSERT INTO transactions.JobDescResponsibility(JobDescriptionID, MainTask, Activities, CreatedBy, CreatedDate, UpdatedBy, UpdatedDate,ProgramID) VALUES(@JobDescriptionID, @MainTask, @Activities, @CreatedBy, @CreatedDate, @UpdatedBy, @UpdatedDate,@ProgramID)";
                        SqlCommand cmdResponsibility = new SqlCommand(query, conn);
                        cmdResponsibility.CommandType = CommandType.Text;
                        cmdResponsibility.Parameters.Add("@JobDescriptionID", SqlDbType.Int).Value = domain.JobDescID;
                        cmdResponsibility.Parameters.Add("@MainTask", SqlDbType.VarChar).Value = (object)data.MainTask ?? DBNull.Value;
                        cmdResponsibility.Parameters.Add("@Activities", SqlDbType.Text).Value = (object)data.Activities ?? DBNull.Value;
                        cmdResponsibility.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)data.CreatedBy ?? DBNull.Value;
                        cmdResponsibility.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)data.CreatedDate ?? DBNull.Value;
                        cmdResponsibility.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)data.UpdatedBy ?? DBNull.Value;
                        cmdResponsibility.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)data.UpdatedDate ?? DBNull.Value;

                        cmdResponsibility.Parameters.Add("@ProgramID", SqlDbType.Int).Value = programIDAktif;

                        cmdResponsibility.Transaction = txn;
                        cmdResponsibility.ExecuteNonQuery();
                        cmdResponsibility.Dispose();
                    }

                    #endregion


                    #region Insert Data PI

                    foreach (PerformanceIndicator data in listPI)
                    {

                        #region Ambil ResponsibilityID

                        string queryResponPI = @"SELECT ResponsibilityID FROM transactions.JobDescResponsibility WHERE MainTask=@MainTask AND JobDescriptionID=@JobDescriptionID AND ProgramID=@ProgramID";

                        SqlCommand cmdResponPI = new SqlCommand(queryResponPI, conn);
                        cmdResponPI.Parameters.Add("@MainTask", SqlDbType.VarChar).Value = data.ParentName;
                        cmdResponPI.Parameters.Add("@JobDescriptionID", SqlDbType.VarChar).Value = domain.JobDescID;

                        cmdResponPI.Parameters.Add("@ProgramID", SqlDbType.Int).Value = programIDAktif;

                        cmdResponPI.Transaction = txn;
                        var responId = cmdResponPI.ExecuteScalar();
                        int resultresponId = responId == DBNull.Value ? 0 : int.Parse(responId.ToString());
                        cmdResponPI.Dispose();

                        #endregion


                        query = "INSERT INTO transactions.PerformanceIndicator(JobDescResponsibilityID, PI, CreatedBy, CreatedDate, UpdatedBy, UpdatedDate,ProgramID) VALUES(@JobDescResponsibilityID, @PI, @CreatedBy, @CreatedDate, @UpdatedBy, @UpdatedDate,@ProgramID)";
                        SqlCommand cmdPI = new SqlCommand(query, conn);
                        cmdPI.CommandType = CommandType.Text;
                        cmdPI.Parameters.Add("@JobDescResponsibilityID", SqlDbType.Int).Value = resultresponId;
                        cmdPI.Parameters.Add("@PI", SqlDbType.VarChar).Value = data.PI;
                        cmdPI.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)data.CreatedBy ?? DBNull.Value;
                        cmdPI.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)data.CreatedDate ?? DBNull.Value;
                        cmdPI.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)data.UpdatedBy ?? DBNull.Value;
                        cmdPI.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)data.UpdatedDate ?? DBNull.Value;

                        cmdPI.Parameters.Add("@ProgramID", SqlDbType.Int).Value = programIDAktif;


                        cmdPI.Transaction = txn;
                        cmdPI.ExecuteNonQuery();
                        cmdPI.Dispose();
                    }

                    #endregion

                    if (status == "review")
                    {
                        #region Insert Data  Review JobDesc

                        query = "INSERT INTO transactions.ReviewJobDescription(IsReview, JobDescID, ProgramID, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy) VALUES(@IsReview, @JobDescID, @ProgramID, @CreatedDate, @CreatedBy, @UpdatedDate, @UpdatedBy)";
                        SqlCommand cmdReview = new SqlCommand(query, conn);
                        cmdReview.CommandType = CommandType.Text;
                        cmdReview.Parameters.Add("@IsReview", SqlDbType.Bit).Value = (object)domainReview.IsReview ?? DBNull.Value;
                        cmdReview.Parameters.Add("@JobDescID", SqlDbType.Int).Value = (object)domainReview.JobDescID ?? DBNull.Value;
                        cmdReview.Parameters.Add("@ProgramID", SqlDbType.Int).Value = (object)domainReview.ProgramID ?? DBNull.Value;
                        cmdReview.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)domainReview.CreatedDate ?? DBNull.Value;
                        cmdReview.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)domainReview.CreatedBy ?? DBNull.Value;
                        cmdReview.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)domainReview.UpdatedDate ?? DBNull.Value;
                        cmdReview.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)domainReview.UpdatedBy ?? DBNull.Value;

                        cmdReview.Transaction = txn;
                        cmdReview.ExecuteNonQuery();
                        cmdReview.Dispose();

                        #endregion
                    }

                    //#region Update Data JobTitle

                    //query = "UPDATE master.JobTitle SET MinEducation = @MinEducation, Major = @Major, WorkingExperience = @WorkingExperience,  FunctionalCompetency = @FunctionalCompetency, Challenge = @Challenge, Authority = @Authority, UpdatedDate = @UpdatedDate, UpdatedBy = @UpdatedBy WHERE PositionID = @PositionID AND ProgramID=@ProgramID";
                    //SqlCommand cmdJobTitle = new SqlCommand(query, conn);

                    //cmdJobTitle.Parameters.Add("@PositionID", SqlDbType.Int).Value = (object)objJobTitle.PositionID ?? DBNull.Value;
                    //cmdJobTitle.Parameters.Add("@MinEducation", SqlDbType.VarChar).Value = (object)objJobTitle.MinEducation ?? DBNull.Value;
                    //cmdJobTitle.Parameters.Add("@Major", SqlDbType.VarChar).Value = (object)objJobTitle.Major ?? DBNull.Value;
                    //cmdJobTitle.Parameters.Add("@WorkingExperience", SqlDbType.Text).Value = (object)objJobTitle.WorkingExperience ?? DBNull.Value;
                    //cmdJobTitle.Parameters.Add("@FunctionalCompetency", SqlDbType.Text).Value = (object)objJobTitle.FunctionalCompetency ?? DBNull.Value;
                    //cmdJobTitle.Parameters.Add("@Challenge", SqlDbType.Text).Value = (object)objJobTitle.Challenge ?? DBNull.Value;
                    //cmdJobTitle.Parameters.Add("@Authority", SqlDbType.Text).Value = (object)objJobTitle.Authority ?? DBNull.Value;
                    //cmdJobTitle.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)objJobTitle.UpdatedDate ?? DBNull.Value;
                    //cmdJobTitle.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)objJobTitle.UpdatedBy ?? DBNull.Value;

                    //cmdJobTitle.Parameters.Add("@ProgramID", SqlDbType.Int).Value = programIDAktif;

                    //cmdJobTitle.Transaction = txn;
                    //cmdJobTitle.ExecuteNonQuery();
                    //cmdJobTitle.Dispose();


                    //#endregion


                    result = true;
                    txn.Commit();

                }
                catch (Exception ex)
                {
                    txn.Rollback();
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }
            }

            return result;
        }


        public DataTable GetDataTableReport(string positionID,string programID)
        {
            DataTable dt = new DataTable();

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {

                    StringBuilder sbQuery = new StringBuilder();


                    sbQuery.AppendLine(@"SELECT  DISTINCT
B.PositionID 
,B.PositionName
,C.DepartmentDesc
,(SELECT TOP 1 Z.PositionName FROM master.Position Z WHERE Z.PositionID = B.PositionSuperior) Atasan
,A.Location
,convert(VARCHAR(MAX),A.JobPurpose) JobPurpose
,D.MainTask
,convert(VARCHAR(MAX),D.Activities) +CHAR(13)+CHAR(10)+'PI:' Activities
,D.ResponsibilityID
,E.PI
,F.Department HubunganInternal
,convert(VARCHAR(MAX),F.Purpose) TujuanInternal
,F.JobDescRelationShipID JobDescRelationShipIDInternal
,G.Department HubunganExternal
,convert(VARCHAR(MAX),G.Purpose) TujuanExternal
,G.JobDescRelationShipID JobDescRelationShipIDExternal
,H.MinEducation +' dari ' + H.Major MinEducation
,convert(VARCHAR(MAX),H.WorkingExperience)  WorkingExperience
,convert(VARCHAR(MAX),J.CompetncyDesc) CompetncyDesc
,K.Level
,J.GroupCompetency
,J.ComptID
,convert(VARCHAR(MAX),A.FunctionalCompetency) FunctionalCompetency
,convert(VARCHAR(MAX),A.Challenge)  Challenge
,A.Opex
,A.Capex
--,'Langsung : '+CHAR(13)+CHAR(10)+convert(VARCHAR(MAX),A.DirectSubordinates) +CHAR(13)+CHAR(10)  +CHAR(13)+CHAR(10) + 'Tidak Langsung : '+CHAR(13)+CHAR(10)+convert(VARCHAR(MAX),A.IndirectSubordinates)  DirectSubordinates

,CASE WHEN convert(VARCHAR(MAX),A.DirectSubordinates)='' THEN ' ' ELSE 
'Langsung : '+CHAR(13)+CHAR(10)+convert(VARCHAR(MAX),A.DirectSubordinates) +CHAR(13)+CHAR(10)  +CHAR(13)+CHAR(10) 
 + CASE WHEN  convert(VARCHAR(MAX),A.IndirectSubordinates)='' THEN ' ' ELSE 'Tidak Langsung : '+CHAR(13)+CHAR(10)+convert(VARCHAR(MAX),A.IndirectSubordinates)   END 
 END DirectSubordinates

,'Tidak Langsung : '+CHAR(13)+CHAR(10)+convert(VARCHAR(MAX),A.IndirectSubordinates)  IndirectSubordinates
,convert(VARCHAR(MAX),A.Authority) Authority 
,A.JobDescID
,E.PerformanceIndicatorID
,B.ImageOrganisasi
,convert(varchar, A.ApprovedDate , 105) ApprovedDate
FROM transactions.JobDescription A
LEFT JOIN master.Position B ON A.PositionID =B.PositionID
LEFT JOIN master.Department C ON B.DepartmentID = C.DeptID
LEFT JOIN transactions.JobDescResponsibility D ON A.JobDescID = D.JobDescriptionID
LEFT JOIN transactions.PerformanceIndicator E ON D.ResponsibilityID = E.JobDescResponsibilityID
LEFT JOIN transactions.JobDescRelationShipInternal F ON A.JobDescID=F.JobDescriptionID
LEFT JOIN transactions.JobDescRelationShipExternal G ON A.JobDescID = G.JobDescriptionID
LEFT JOIN master.JobTitle H ON A.PositionID = H.PositionID
LEFT JOIN master.JobManagerialCompetency I ON A.PositionID = I.PositionID
LEFT JOIN master.Competency J ON I.CompetencyID =J.ComptID 
LEFT JOIN master.LevelCompetency K ON K.CompetencyID =I.CompetencyID AND I.LevelCompetencyID=K.ID
WHERE A.PositionID=@PositionID 
AND A.ProgramID=@ProgramID
AND D.ProgramID=@ProgramID
AND E.ProgramID=@ProgramID
AND F.ProgramID=@ProgramID
AND G.ProgramID=@ProgramID
AND H.ProgramID=@ProgramID
AND I.ProgramID=@ProgramID
"); 



                    cmd.CommandText = sbQuery.ToString();
                    cmd.Parameters.Add("@PositionID", SqlDbType.Int).Value = int.Parse(positionID);
                    cmd.Parameters.Add("@ProgramID", SqlDbType.Int).Value = int.Parse(programID);
                    //cmd.Parameters.Add("@DepartmentID", SqlDbType.Int).Value = departmentID;
                    //cmd.BindByName = true;


                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = cmd;

                    try
                    {
                        conn.Open();
                        da.Fill(dt);
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }

                }
            }

            return dt;

        }

        public List<JobDescription> GetListByPositionID(int positionID)
        {
            List<JobDescription> tt = new List<JobDescription>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "SELECT JobDescID, RowStatus, PositionID, DepartmentID, Location, JobPurpose, Capex, Opex, DirectSubordinates, IndirectSubordinates, AppAccountID, CreatedBy, CreatedDate, UpdatedBy, UpdatedDate FROM transactions.JobDescription WHERE PositionID=@PositionID";
                    cmd.Parameters.Add("@PositionID", SqlDbType.Int).Value = positionID;
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                JobDescription t = new JobDescription();
                                t.JobDescID = dr[JobDescription.Fields.JobDescID] == DBNull.Value ? 0 : int.Parse(dr[JobDescription.Fields.JobDescID].ToString());
                                t.RowStatus = dr[JobDescription.Fields.RowStatus] == DBNull.Value ? false : bool.Parse(dr[JobDescription.Fields.RowStatus].ToString());
                                t.PositionID = dr[JobDescription.Fields.PositionID] == DBNull.Value ? 0 : int.Parse(dr[JobDescription.Fields.PositionID].ToString());
                                t.DepartmentID = dr[JobDescription.Fields.DepartmentID] == DBNull.Value ? 0 : int.Parse(dr[JobDescription.Fields.DepartmentID].ToString());
                                t.Location = dr[JobDescription.Fields.Location] == DBNull.Value ? string.Empty : dr[JobDescription.Fields.Location].ToString();
                                t.JobPurpose = dr[JobDescription.Fields.JobPurpose] == DBNull.Value ? string.Empty : dr[JobDescription.Fields.JobPurpose].ToString();
                                t.Capex = dr[JobDescription.Fields.Capex] == DBNull.Value ? string.Empty : dr[JobDescription.Fields.Capex].ToString();
                                t.Opex = dr[JobDescription.Fields.Opex] == DBNull.Value ? string.Empty : dr[JobDescription.Fields.Opex].ToString();
                                t.DirectSubordinates = dr[JobDescription.Fields.DirectSubordinates] == DBNull.Value ? string.Empty : dr[JobDescription.Fields.DirectSubordinates].ToString();
                                t.IndirectSubordinates = dr[JobDescription.Fields.IndirectSubordinates] == DBNull.Value ? string.Empty : dr[JobDescription.Fields.IndirectSubordinates].ToString();
                                t.AppAccountID = dr[JobDescription.Fields.AppAccountID] == DBNull.Value ? (int?)null : int.Parse(dr[JobDescription.Fields.AppAccountID].ToString());
                                t.CreatedBy = dr[JobDescription.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[JobDescription.Fields.CreatedBy].ToString();
                                t.CreatedDate = dr[JobDescription.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[JobDescription.Fields.CreatedDate].ToString());
                                t.UpdatedBy = dr[JobDescription.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[JobDescription.Fields.UpdatedBy].ToString();
                                t.UpdatedDate = dr[JobDescription.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[JobDescription.Fields.UpdatedDate].ToString());
                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }


        public List<JobDescription> GetListByPositionIDCluaseIN(string positionID)
        {
            List<JobDescription> tt = new List<JobDescription>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "SELECT JobDescID, RowStatus, PositionID, DepartmentID, Location, JobPurpose, Capex, Opex, DirectSubordinates, IndirectSubordinates, AppAccountID, CreatedBy, CreatedDate, UpdatedBy, UpdatedDate,ReviewBy,ReviewDate FROM transactions.JobDescription WHERE PositionID IN" + positionID + " ORDER BY PositionID ASC";
                    //cmd.Parameters.Add("@PositionID", SqlDbType.Int).Value = positionID;
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                JobDescription t = new JobDescription();
                                t.JobDescID = dr[JobDescription.Fields.JobDescID] == DBNull.Value ? 0 : int.Parse(dr[JobDescription.Fields.JobDescID].ToString());
                                t.RowStatus = dr[JobDescription.Fields.RowStatus] == DBNull.Value ? false : bool.Parse(dr[JobDescription.Fields.RowStatus].ToString());
                                t.PositionID = dr[JobDescription.Fields.PositionID] == DBNull.Value ? 0 : int.Parse(dr[JobDescription.Fields.PositionID].ToString());
                                t.DepartmentID = dr[JobDescription.Fields.DepartmentID] == DBNull.Value ? 0 : int.Parse(dr[JobDescription.Fields.DepartmentID].ToString());
                                t.Location = dr[JobDescription.Fields.Location] == DBNull.Value ? string.Empty : dr[JobDescription.Fields.Location].ToString();
                                t.JobPurpose = dr[JobDescription.Fields.JobPurpose] == DBNull.Value ? string.Empty : dr[JobDescription.Fields.JobPurpose].ToString();
                                t.Capex = dr[JobDescription.Fields.Capex] == DBNull.Value ? string.Empty : dr[JobDescription.Fields.Capex].ToString();
                                t.Opex = dr[JobDescription.Fields.Opex] == DBNull.Value ? string.Empty : dr[JobDescription.Fields.Opex].ToString();
                                t.DirectSubordinates = dr[JobDescription.Fields.DirectSubordinates] == DBNull.Value ? string.Empty : dr[JobDescription.Fields.DirectSubordinates].ToString();
                                t.IndirectSubordinates = dr[JobDescription.Fields.IndirectSubordinates] == DBNull.Value ? string.Empty : dr[JobDescription.Fields.IndirectSubordinates].ToString();
                                t.AppAccountID = dr[JobDescription.Fields.AppAccountID] == DBNull.Value ? (int?)null : int.Parse(dr[JobDescription.Fields.AppAccountID].ToString());
                                t.CreatedBy = dr[JobDescription.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[JobDescription.Fields.CreatedBy].ToString();
                                t.CreatedDate = dr[JobDescription.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[JobDescription.Fields.CreatedDate].ToString());
                                t.UpdatedBy = dr[JobDescription.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[JobDescription.Fields.UpdatedBy].ToString();
                                t.UpdatedDate = dr[JobDescription.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[JobDescription.Fields.UpdatedDate].ToString());
                                t.ReviewBy = dr[JobDescription.Fields.ReviewBy] == DBNull.Value ? string.Empty : dr[JobDescription.Fields.ReviewBy].ToString();
                                t.ReviewDate = dr[JobDescription.Fields.ReviewDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[JobDescription.Fields.ReviewDate].ToString());

                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }

        public bool Approve(List<JobDescription> list)
        {
            bool result = false;

            using (SqlConnection conn = new SqlConnection(connectionString))
            {

                conn.Open();
                SqlTransaction txn = conn.BeginTransaction();
                try
                {
                  


                    #region Update Approve 

                    foreach (JobDescription data in list)
                    {
                        string query = "UPDATE transactions.JobDescription SET UpdatedBy = @UpdatedBy, UpdatedDate = @UpdatedDate, ApprovedBy = @ApprovedBy, ApprovedDate = @ApprovedDate WHERE JobDescID = @JobDescID";
                        SqlCommand cmd = new SqlCommand(query, conn);
                        cmd.CommandType = CommandType.Text;
                        cmd.Parameters.Add("@JobDescID", SqlDbType.Int).Value = data.JobDescID;

                        cmd.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)data.UpdatedBy ?? DBNull.Value;
                        cmd.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)data.UpdatedDate ?? DBNull.Value;
                        cmd.Parameters.Add("@ApprovedBy", SqlDbType.VarChar).Value = (object)data.ApprovedBy ?? DBNull.Value;
                        cmd.Parameters.Add("@ApprovedDate", SqlDbType.DateTime).Value = (object)data.ApprovedDate ?? DBNull.Value;

                        cmd.Transaction = txn;
                        cmd.ExecuteNonQuery();
                        cmd.Dispose();
                    }

                    #endregion


                    result = true;
                    txn.Commit();

                }
                catch (Exception ex)
                {
                    txn.Rollback();
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }
            }

            return result;
        }
    }
}
